package com.renbang.utils.excel;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelImport {
	
	public static List<TreeMap<String, Object>> readExcelAndCsv(File file)
			throws Exception {
		String endWith = file.getName().lastIndexOf(".") == -1 ? "" : file.getName().substring(file.getName().lastIndexOf(".") + 1);
		if ("xls".equals(endWith)) {
			return read2003Excel(file);
		} else if ("xlsx".equals(endWith)) {
			return read2007Excel(file);
		} else if("csv".equals(endWith)){
			return readCsv(file);
		}else{
			throw new IOException("不支持的文件类型");
		}
		
	}
	/**
	 * 
	 * @Description:
	 * @author:user
	 * @param csv
	 * @return ArrayList<TreeMap<String,Object>>
	 * @date 2017年4月28日
	 */
	public static List<TreeMap<String, Object>> readCsv(File file) throws Exception {	
		ArrayList<TreeMap<String, Object>> list = new ArrayList<TreeMap<String, Object>>();
		try {
			String code=codeString(null,file);
			BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file),code));
			String line = "";
			while ((line=br.readLine())!=null) {
				TreeMap<String, Object> pd = new TreeMap<String, Object>();
				String[] Str = line.split(",");
				for(int i=0;i<Str.length;i++ ){
					pd.put("td"+i, Str[i]);
				}
				list.add(pd);
			}
			br.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 
	 * @Description:2003
	 * @author:user
	 * @param ExcelImport.java
	 * @return List<List<Object>>
	 * @date 2017年4月28日
	 */
	private static List<TreeMap<String, Object>> read2003Excel(File file) throws IOException {
		List<TreeMap<String, Object>> list = new ArrayList<TreeMap<String, Object>>();
		HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
		HSSFSheet sheet = hwb.getSheetAt(0);
		Object value = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		Integer rowsNum = sheet.getPhysicalNumberOfRows();// 读取的行数
		Integer cellsNum ;// 读取的列数
		for (int i = sheet.getFirstRowNum(); i <= rowsNum; i++) {
			row = sheet.getRow(i);
 			if (row == null) {
				continue;
			}
 			cellsNum = (int) row.getLastCellNum();
			if (row.getFirstCellNum() < 0) {
				continue;
			}
			TreeMap<String, Object> data = new TreeMap<String, Object>();
			for (int j = 0; j < cellsNum; j++) {// row.getFirstCellNum()
				cell = row.getCell(j);
				if (cell == null) {
					value = null;
				} else {
					DecimalFormat df = new DecimalFormat("0");// 格式化 number
					// 字符
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
//					DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if ("@".equals(cell.getCellStyle().getDataFormatString())) {
							value = df.format(cell.getNumericCellValue());
						} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
							value = df.format(cell.getNumericCellValue());
						} else {
							value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = cell.getBooleanCellValue();
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					default:
						value = cell.toString();
					}
				}
				data.put("td"+j, value);
			}
			list.add(data);
		}
		return list;
	}
	/**
	 * 
	 * @Description:2007
	 * @author:user
	 * @param 
	 * @return List<List<Object>>
	 * @date 2017年4月28日
	 */
	private static List<TreeMap<String, Object>> read2007Excel(File file) throws IOException {
		List<TreeMap<String, Object>> list = new ArrayList<TreeMap<String, Object>>();
		// 构造 XSSFWorkbook 对象
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
		//第一个工作簿
		XSSFSheet sheet = xwb.getSheetAt(0);
		Object value = null;
		XSSFRow row = null;
		XSSFCell cell = null;
		Integer rowsNum = sheet.getPhysicalNumberOfRows();// 读取的行数
		Integer cellsNum ;// 读取的列数
		for (int i = sheet.getFirstRowNum(); i <= rowsNum; i++) {
			row = sheet.getRow(i);
			if (row == null) 
				continue;
			cellsNum= (int) row.getLastCellNum();
			if (row.getFirstCellNum() < 0) 
				continue;
			TreeMap<String, Object> data = new TreeMap<String, Object>();
			for (int j = 0; j < cellsNum; j++) {
				cell = row.getCell(j);
				if (cell == null) {
					value = null;
				} else {
					DecimalFormat df = new DecimalFormat("0");// 格式化 number
					// 字符
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
					//DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if ("@".equals(cell.getCellStyle().getDataFormatString())) {
							value = df.format(cell.getNumericCellValue());
						} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
							value = df.format(cell.getNumericCellValue());
						} else {
							value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = cell.getBooleanCellValue();
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					default:
						value = cell.toString();
					}
				}
				data.put("td"+j, value);
			}
			list.add(data);
		}
		return list;
	}
	
	/**
	 * @Description:得到文本的编码格式并且返回防止修改编码格式后中文乱码
	 * @author:chen
	 * @param fileName 文件路径
	 * @param file	或者文件
	 * @return String
	 * @date 2017年4月28日
	 */
	public static String codeString(String fileName,File file) throws Exception{  
		FileInputStream fis = null;
		//传文件或者文件路径都可以
		if(fileName!=null){
			fis=new FileInputStream(fileName);
		}else if(file!=null){
			fis=new FileInputStream(file);
		}
	    BufferedInputStream bin = new BufferedInputStream(fis);
	    int p = (bin.read() << 8) + bin.read();  
	    String code = null;  
	    switch (p) {  
	        case 0xefbb:  
	            code = "UTF-8";  
	            break;  
	        case 0xfffe:  
	            code = "Unicode";  
	            break;  
	        case 0xfeff:  
	            code = "UTF-16BE";  
	            break;  
	        default:  
	            code = "GBK";
	    }  
	    bin.close();
	    return code;  
	}  
	
} 

